const router = require('express').Router();
const db = require('./../config/dbcofnig')
// 1.获取医生职称数据
router.route("/getJobs").get((req, res) => {
  let sql = `SELECT * FROM jobs;`;
  let params = [];
  let cb = (err, data) => {
    // console.log(err)
    // console.log(data)
    if (err == null) {
      res.json({
        code: 200,
        msg: '查询成功',
        data: data
      })
    } else {
      res.json({
        code: 500,
        msg: '查询失败',
        data: []
      })
    }
  }
  db.connect(sql, params, cb);

});


// 2. 添加职称
router.route('/addJobs').get((req, res) => {
  let { jobname, jobdes } = req.query;
  // 如果参数不规范，就响应以下结果 
  if (!(/^.{1,20}$/.test(jobname) && /^.{1,50}$/.test(jobdes))) {
    res.json({
      code: 40001,
      msg: '参数必须'
    })
    return; // 一个请求，只能做一次响应，就必须执行 return.
  }

  // 前判断后，表示有参数，通过验证，不执行前面的IF.就执行后面的代码
  let sql = `insert into jobs values(null,?,?);`;
  let params = [jobname, jobdes]
  let cb = (err, data) => {
    // console.log(err);
    // console.log(data);

    /* 
        OkPacket {
      fieldCount: 0,
      affectedRows: 1,
      insertId: 9,  // 新添加数据的自增长的ID值
      serverStatus: 2,
      warningCount: 0,
      message: '',
      protocol41: true,
      changedRows: 0
    }
    */
    if (err === null) {
      res.json({
        code: 200,
        msg: '添加成功'
      })
    } else {
      res.json({
        code: 40002,
        msg: '添加失败'
      })
    }
  }
  db.connect(sql, params, cb)

});

// 3. 删除职称
router.route("/deleteJobs").post((req, res) => {
  let jid = req.body.jid;
  console.log(req.body)

  if (!jid) {
    res.json({
      code: 40001,
      msg: '缺少ID参数'
    });
    return;
  }

  let sql = `delete from jobs where jid=?;`;
  let params = [jid];
  let cb = (err, data) => {
    console.log(err)
    console.log(data)
    /* 
    OkPacket {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0
}
    */
    //  添加、删除、修改表的数据，只需要判断err===null.
    if (err === null) {
      res.json({
        code: 200,
        msg: '删除成功'
      });
    } else {
      res.json({
        code: 40002,
        msg: '删除失败'
      });
    }
  }
  db.connect(sql, params, cb);


});

// 4. 查询一个职称的信息
router.route('/getJobInfo').post((req, res) => {
  let { jid } = req.body;

  if (!jid) {
    res.json({
      code: 4001,
      msg: 'id时必须的'
    });
    return;
  }

  let sql = `select * from jobs where jid=?;`;
  let params = [jid];
  let cb = (err, data) => {
    if (err === null) {
      res.json({
        code: 200,
        msg: '请求成功',
        data,  // data属性的值，就是形参data变量的值。
      });
    } else {
      res.json({
        code: 4002,
        msg: '请求失败'
      });
    }
  }
  db.connect(sql, params, cb);
})

// 5. 修改、更新职称信息
router.route('/updateJobInfo').post((req, res) => {
  let { jid, jname, jdes } = req.body;
  if (!jid) {
    res.json({
      code: 4001, msg: 'jid参数不能缺少'
    })
    return;
  }
  if (!jname) {
    res.json({
      code: 4001, msg: 'jname参数不能缺少'
    })
    return;
  }
  if (!jdes) {
    res.json({
      code: 4001, msg: 'jdes参数不能缺少'
    })
    return;
  }
  let sql = `update jobs set jname=?, jdes=? where jid =?;`;
  let params = [jname, jdes, jid]
  let cb = (err, data) => {
    if (err === null) {
      res.json({
        code: 200, msg: '修改成功'
      })
    } else {
      res.json({
        code: 4002, msg: '修改失败'
      })
    }
  }

  db.connect(sql, params, cb)
})

// 6. 分页查询职称数据
router.route('/getJobsPage').get((req, res) => {

  let { nowpage, pagesize } = req.query;
  if (!nowpage || !pagesize) {
    res.json({
      code: 4001,
      msg: '没有分页的参数'
    });
    return;
  }
  // console.log(nowpage, pagesize)
  let startIndex = (nowpage - 1) * pagesize;
  // console.log(startIndex);
  let sql = `select * from jobs limit ?,?; select count(*) as count from jobs;`;
  let params = [startIndex, Number(pagesize)];
  let cb = (err, data) => {
    console.log(err);
    console.log(data);
    if (err === null) {
      res.json({
        code: 200,
        msg: '查询成功',
        data: data[0],
        count: data[1][0].count  // 多SQL查询，返回2维数组，要使用2个下标来表示数组的一个元素。
      });
    } else {
      res.json({
        code: 4002,
        msg: '查询失败',
      })
    }
  }

  db.connect(sql, params, cb)
})



module.exports = router;
